﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Security.Cryptography;
using TravelSite.Entities;
using Dapper;

namespace TravelSite.DAL.Repositories
{
    public class UserRepository : BaseRepository<User>
    {
        protected override string TableName
        {
            get { return "[User]"; }
        }

        public IEnumerable<User> FindAll(bool removeDeleted)
        {
            string strSQL = @"  SELECT u.Id, u.UserId, u.UserName, u.FirstName, u.LastName, u.Email, u.IsAdmin, u.IsActive, u.Deleted
                    FROM [User] (nolock) u ";
            if (removeDeleted)
                strSQL += "WHERE u.Deleted = 0";
            return Connection.Query<User>(strSQL);
        }

        public IEnumerable<User> Search(string lastName, string email, string accessLevel, string status)
        {
            return Connection.Query<User>("spUser_Search @Email, @LastName, @AccessLevel, @Status", new { Email = email, LastName = lastName, AccessLevel = (accessLevel == "ALL" ? null : accessLevel), Status = (status == "ALL" ? null : status) });
        }

        public override User Find(int id)
        {
            return Connection.Query<User>(
                @"  SELECT u.Id, u.UserId, u.UserName, u.FirstName, u.LastName, u.Email, u.IsAdmin, u.IsActive, u.Deleted, u.AuthCode, am.Password, u.EmailSettingId
                    FROM [User] (nolock) u left join aspnet_Membership (nolock) as am on u.UserId = am.UserId
                    WHERE u.Id = @userId", new { userId = id }).SingleOrDefault();
        }

        public User Find(Guid userId)
        {
            return Connection.Query<User>(
                @"  SELECT u.Id, u.UserId, u.UserName, u.FirstName, u.LastName, u.Email, u.IsAdmin, u.IsActive, u.Deleted, u.AuthCode, u.EmailSettingId
                    FROM [User] (nolock) u
                    WHERE u.UserId = @userId", new { userId = userId }).SingleOrDefault();
        }

        public User Find(string userName)
        {
            return Connection.Query<User>(
                @"  SELECT u.Id, u.UserId, u.UserName, u.FirstName, u.LastName, u.Email, u.IsAdmin, u.IsActive, u.Deleted, u.AuthCode, u.EmailSettingId
                    FROM [User] (nolock) u
                    WHERE u.UserName = @userName", new { userName = userName }).SingleOrDefault();
        }

        public User FindByAuthCode(Guid authCode)
        {
            return Connection.Query<User>(
                @"  SELECT u.Id, u.UserId, u.UserName, u.FirstName, u.LastName, u.Email, u.IsAdmin, u.IsActive, u.Deleted, u.AuthCode, u.EmailSettingId
                    FROM [User] (nolock) u
                    WHERE u.AuthCode = @AuthCode", new { AuthCode = authCode }).SingleOrDefault();
        }

        public void SetAuthCode(Guid userId)
        {
            Guid authCode = Guid.NewGuid();
            Connection.Execute("update [User] set AuthCode = @AuthCode where UserId = @UserId", new { AuthCode = authCode, UserId = userId });
        }
        
        public void AddManual(User newUser, string password)
        {
            Connection.Execute("spUser_Add @UserName, @FirstName, @LastName, @Email, @IsAdmin, @Password, @PasswordSalt, @EmailSettingId",
                new
                {
                    UserName = newUser.UserName,
                    FirstName = newUser.FirstName,
                    LastName = newUser.LastName,
                    Email = newUser.Email,
                    IsAdmin = newUser.IsAdmin,
                    Password = password,
                    PasswordSalt = GeneratePasswordSalt(),
                    EmailSettingId = newUser.EmailSettingId
                });
        }

        private string GeneratePasswordSalt()
        {
            RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
            byte[] buff = new byte[32];
            rng.GetBytes(buff);
            return Convert.ToBase64String(buff);
        }

        public new string Update(User updateObject)
        {
            return Connection.Query<string>("spUser_Upd @UserId, @UserName, @FirstName, @LastName, @Email, @IsAdmin, @Deleted, @EmailSettingId",
                new
                {
                    UserId = updateObject.Id,
                    UserName = updateObject.UserName,
                    FirstName = updateObject.FirstName,
                    LastName = updateObject.LastName,
                    Email = updateObject.Email,
                    IsAdmin = updateObject.IsAdmin,
                    Deleted = updateObject.Deleted,
                    EmailSettingId = updateObject.EmailSettingId
                }).Single();
        }

        public void Activate(int id)
        {
            Connection.Execute(@"UPDATE [User] SET IsActive = 1 WHERE Id = @Id;", new { Id = id });
        }

        public void Deactivate(int id)
        {
            Connection.Execute(@"UPDATE [User] SET IsActive = 0 WHERE Id = @Id;", new { Id = id });
        }

        public override void Delete(int id)
        {
            try
            {
                Connection.Execute("spUser_Delete @Id", new { Id = id });
            }
            catch
            {
                Deactivate(id);
            }
        }

        public IEnumerable<User> AgentsCombo(bool includeAll)
        {
            string strSQL = "";
            if (includeAll)
                strSQL = @"select -1 as Id, 'ALL' as FirstName, '' AS LastName, 0 as SortIndex union all ";
            strSQL += @"select Id, FirstName, LastName, 1 as SortIndex from [User] (nolock) where IsAdmin = 0 order by SortIndex, FirstName, LastName";
            return Connection.Query<User>(strSQL);
        }

        public void LogAdd(Exception ex, HttpContext context, string userName)
        {
            Connection.Execute(@"insert into Log
                        ([EventMessage], [StackTrace], [CurrentUser], [Path], [QueryString], [Browser], [UserHostAddress], [UrlReferrer])
                        values(@EventMessage, @StackTrace, @CurrentUser, @Path, @QueryString, @Browser, @UserHostAddress, @UrlReferrer)",
             new { EventMessage = ex.InnerException != null ? ex.InnerException.Message : ex.Message,
                   StackTrace = ex.InnerException != null ? ex.InnerException.StackTrace : ex.StackTrace,
                   CurrentUser = string.IsNullOrEmpty(userName) ? "" : userName,
                   Path = context.Request.Path,
                   QueryString = context.Request.QueryString.ToString(),
                   Browser = context.Request.Browser.Browser,
                   UserHostAddress = context.Request.UserHostAddress,
                   UrlReferrer = (context.Request.UrlReferrer != null ? context.Request.UrlReferrer.ToString() : "")
             });
        }
    }
}
